
/**
 * 生成SQL查询语句
 */
Ext.define('SqlBuilder', {
	extend : 'Ext.window.Window',
	layout : 'border',
	modal : true,
	maximizable : true,
	title : '指标编辑器',
	iconCls : 'btn-pred',
	height : 600,
	width : 950,
	buttonAlign : 'center',

	initComponent : function() {
		var me = this;
		this.columnData = [];
		this.tableData = [];
		this.columnStore = Ext.create('Ext.data.ArrayStore', {
					autoLoad : true,
					fields : ['oid', 'code', 'name']
				});
		this.tableStore = Ext.create('Ext.data.ArrayStore', {
					autoLoad : true,
					fields : ['oid', 'code', 'name']
				});
		// 数据表
		var topbar1 = new Ext.Toolbar({
					items : [{
								iconCls : 'btn-add',
								text : '选择数据表',
								xtype : 'button',
								scope : this,
								handler : this.addTable
							}]
				});
		this.tableGrid = new KE.GridPanel({
					region : 'center',
					title : '数据表',
					tbar : topbar1,
					id : 'SqlTableGrid',
					selModel : 'rowmodel',
					showPaging : false,
					pageSize : 99999,
					hideHeaders : true,
					store : this.tableStore,
					columns : [{
								header : '中文名',
								dataIndex : 'name',
								width: 150
							}, {
								header : '表名',
								dataIndex : 'code'
							}]
				});
		// 表关系
		var topbar5 = new Ext.Toolbar({
					items : [{
								iconCls : 'btn-add',
								text : '添加关系',
								xtype : 'button',
								scope : this,
								handler : this.addRelation
							}, {
								iconCls : 'btn-del',
								text : '删除关系',
								xtype : 'button',
								scope : this,
								handler : this.delRelation
							}]
				});
		this.relationPanel = new KE.GridPanel({
					region : 'east',
					height : 200,
					collapsible: true,
					collapsed: true,
					width: 650,
					title : '设置表间关系<font color="red">（单表时不需要设置）</font>',
					tbar : topbar5,
					showPaging : false,
					pageSize : 99999,
					store: new Ext.data.ArrayStore({
						autoLoad : true,
						fields : ['oid', 'oid2', 'col', 'col2', 'table1', 'column1', 'eq', 'table2', 'column2']
					}),
					plugins : [new Ext.grid.plugin.CellEditing({
								clicksToEdit : 1
							})],
					columns : [{
						dataIndex : 'oid',
						hidden : true						
					}, {
						dataIndex : 'oid2',
						hidden : true
					}, {
						dataIndex : 'col',
						hidden : true		
					}, {
						dataIndex : 'col2',
						hidden : true		
					}, {
						header : '表名1',
						dataIndex : 'table1',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',
									store : me.tableStore,
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.oid = record.data.oid;
			                            	selRs.data.column1 = ''; // 清空列名1
			                            } 
			                        }  
								})
					}, {
						header : '列名1',
						dataIndex : 'column1',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : me.columnStore,
									listeners : {  
			                            beforequery : function(queryPlan) {
			                            	var selRs = me.relationPanel.getSelection();
			                            	var tabId = selRs[0].data.oid;
			                                queryPlan.combo.store.loadData(me.columnData[tabId]);
			                            },
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.col = record.data.code;
			                            } 
			                        }  
								})
					}, {
						dataIndex : 'eq',
						width : 30
					}, {
						header : '表名2',
						dataIndex : 'table2',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',
									store : me.tableStore,
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.oid2 = record.data.oid;
			                            	selRs.data.column2 = ''; // 清空列名2
			                            } 
			                        }  
								})
					}, {
						header : '列名2',
						dataIndex : 'column2',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : me.columnStore,
									listeners : {  
			                            beforequery : function(queryPlan) {
			                            	var selRs = me.relationPanel.getSelection();
			                            	var tabId = selRs[0].data.oid2;
			                                queryPlan.combo.store.loadData(me.columnData[tabId]);
			                            },
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.col2 = record.data.code;
			                            }  
			                        }  
								})
					}]
				});
		var toppanel = new Ext.panel.Panel({
					region : 'north',
					layout : 'border',
					height: 200,
					items : [this.tableGrid, this.relationPanel]
				});
		// 查询条件
		var topbar2 = new Ext.Toolbar({
					items : [{
								iconCls : 'btn-add',
								text : '添加条件',
								xtype : 'button',
								scope : this,
								handler : this.addQuery
							}, {
								iconCls : 'btn-del',
								text : '删除条件',
								xtype : 'button',
								scope : this,
								handler : this.delQuery
							}]
				});
		this.centerPanel = new KE.GridPanel({
					region : 'center',
					tbar : topbar2,
					title : '查询条件设置',
					id : 'SqlCenterGrid',
					showPaging : false,
					pageSize : 99999,
					store: new Ext.data.ArrayStore({
						autoLoad : true,
						fields : ['oid', 'gx', 'zkh', 'tab', 'col', 'opt', 'val', 'ykh', 'code']
					}),
					plugins : [new Ext.grid.plugin.CellEditing({
								clicksToEdit : 1
							})],
					columns : [{
								dataIndex : 'oid',
								hidden : true,
								hideable: false
							}, {
								dataIndex : 'code',
								hidden : true,
								hideable: false						
							}, {
								header : '关系',
								width: 50,
								dataIndex : 'gx',
								editor : new Ext.form.ComboBox({
									allowBlank : false,									
									editable : false,									
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : [['并且', '并且'], ['或者', '或者']]
								})
							}, {
								header : '左括号',
								dataIndex : 'zkh',
								width: 50,
								hidden : true,
								editor : new Ext.form.ComboBox({
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : [['(', '('], ['( (', '( ('], ['( ( (', '( ( ('], ['( ( ( (', '( ( ( (']]
								})
							}, {
								header : '考核类别',
								dataIndex : 'tab',
								editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',
									store : me.tableStore,
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	selRs.data.oid = record.data.oid;
			                            	selRs.data.col = ''; // 清空列名
			                            } 
			                        }  
								})
							}, {
								header : '考核项',
								dataIndex : 'col',
								editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : true,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : me.columnStore,
									listeners : {  
			                            beforequery : function(queryPlan) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	var tabId = selRs.data.oid;
			                                queryPlan.combo.store.loadData(me.columnData[tabId]);
			                            },
			                            select : function(combo, record) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	selRs.data.code = record.data.code;
			                            }  
			                        }  
								})
							}, {
								header : '关系',
								dataIndex : 'opt',
								width: 80,
								editor : new Ext.form.ComboBox({
									forceSelection : true,
									triggerAction : 'all',
									editable: false,	
									queryMode : 'local',	
									store : [['等于', '等于'], ['不等于', '不等于'], ['大于', '大于'], 
										['大于等于', '大于等于'], ['小于', '小于'], ['小于等于', '小于等于'],
										['包含', '包含'], ['未采集', '未采集'], 
										['已采集', '已采集']],
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	if(record.data.field1=='未采集' || record.data.field1=='已采集') {
			                            		selRs.data.val = '';
			                            	}
			                            }  
			                        }  
								})
							}, {
								header : '项值',
								dataIndex : 'val',
								editor : new Ext.form.ComboBox({
									store : [['{当前用户}','{当前用户}'], ['{当前用户单位}','{当前用户单位}'], 
										['{考核民警}','{考核民警}'], ['{考核民警单位}','{考核民警单位}'], 
										['{考核开始时间}','{考核开始时间}'], ['{考核结束时间}','{考核结束时间}']]
								})
							}, {
								header : '右括号',
								dataIndex : 'ykh',
								hidden : true,
								width: 50,
								editor : new Ext.form.ComboBox({
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : [[')', ')'], [') )', ') )'], [') ) )', ') ) )'], [') ) ) )', ') ) ) )']]
								})
							}]
				});
		this.items = [toppanel, this.centerPanel];
		this.buttons = [{
					text : '确定',
					iconCls : 'btn-ok',
					scope : this,
					handler : this.save
				}, {
					text : '关闭',
					iconCls : 'btn-cancel',
					scope : this,
					handler : this.cancel
				}];

		this.callParent();
		// 获得tableData、columnData
		if(this.textarea.getValue() && this.json.getValue()) {
			Ext.defer(function() {
				var jsonstr = this.json.getValue();
				jsonstr = jsonstr.replaceAll('“', '\"');
				var obj = Ext.decode(jsonstr);
				var tbs = obj.tables;
				var ids = [];
				for(var i=0; i<tbs.length; i++) {
					this.tableData.push([tbs[i].oid, tbs[i].code, tbs[i].name]);
					ids.push(tbs[i].oid);
					this.tableGrid.store.loadData(this.tableData);
				}
				if(tbs.length > 1) {
					me.relationPanel.setCollapsed(false);
				}
				// ajax请求获得各表的字段信息
				$postAjax({
					async: true,
					url : __ctxPath + '/jxkh/listColumnAppTable.do',
					params: {
						ids: ids
					},
					callback: function(json) {
						me.columnData = json;
					}
				});
				// 将表名、表间关系和查询条件写入gridpanel	
				var rts = obj.relations;
				for(var i=0; i<rts.length; i++) {
					var rec = [{oid: rts[i].oid, oid2: rts[i].oid2, table1: rts[i].table1, 
						column1: rts[i].column1, eq: '=', table2: rts[i].table2, 
						column2: rts[i].column2, col: rts[i].col, col2: rts[i].col2}];
					var grid = this.relationPanel; 
					grid.store.insert(grid.store.getCount(), rec);
				}
				var qys = obj.querys;
					for(var i=0; i<qys.length; i++) {
					var rec = [{oid: qys[i].oid, code: qys[i].code, gx: qys[i].gx, zkh: qys[i].zkh, tab: qys[i].tab, 
						col: qys[i].col, opt: qys[i].opt, val: qys[i].val, ykh: qys[i].ykh}];
					var grid = this.centerPanel; 
					grid.store.insert(grid.store.getCount(), rec);
				}				
			}, 200, this);
			
			
		}
		// 然后将相关值填写表格中
	},
	// 取消
	cancel : function() {
		this.close();
	},
	addTable : function() {
		var me = this;
		AppTableSelector.getView(function(ids, codes, names) {
					ids = ids.split(',');
					codes = codes.split(',');
					names = names.split('，');
					if(ids.length > 1) {
						me.relationPanel.setCollapsed(false);
					}
					var arr = [];
					for (var i = 0; i < ids.length; i++) {
						arr.push([ids[i], codes[i], names[i]]);
					}
					me.tableGrid.store.loadData(arr);
					me.tableData = arr;
					// ajax请求获得各表的字段信息
					$postAjax({
						async: true,
						url : __ctxPath + '/jxkh/listColumnAppTable.do',
						params: {
							ids: ids
						},
						callback: function(json) {
							me.columnData = json;
						}
					});
				}, false).show();
	},
	// 增加关系
	addRelation: function() {
		var rec = [{oid: '', oid2: '', table1: '', column1: '', eq: '=', table2: '', column2: ''}];
		var grid = this.relationPanel; 
		grid.store.insert(grid.store.getCount(), rec);
	},
	// 删除关系
	delRelation: function() {
		var selRs = this.relationPanel.getSelection();
		if(selRs.length == 0) {
			Ext.ux.Toast.msg('操作信息', '请选择要删除的记录');
			return;
		}
		this.relationPanel.store.remove(selRs);
	},
	// 增加查询
	addQuery: function() {
		var rec = [{oid: '', gx: '并且', zkh: '', tab: '', col: '', opt: '已采集', val: '', ykh: '', code:''}];
		var grid = this.centerPanel; 
		grid.store.insert(grid.store.getCount(), rec);	
	},
	// 删除查询
	delQuery: function() {
		var selRs = this.centerPanel.getSelection();
		if(selRs.length == 0) {
			Ext.ux.Toast.msg('操作信息', '请选择要删除的记录');
			return;
		}
		this.centerPanel.store.remove(selRs);
	},
	// 确定
	save: function() {
		var tables = [];
		var relations = [];
		var querys = [];
		var html = '';
		// 1、获得表名
		var store1 = this.tableGrid.store;
		var cnt = store1.getCount();
		if(cnt == 0) {
			Ext.ux.Toast.msg('操作信息', '没有要操作的数据表');
			return;
		}
		for(var i=0; i<cnt; i++) {
			var data = store1.getAt(i).data;
			tables.push(data);			
		}
		
		//2、获得关系
		var store2 = this.relationPanel.store;
		cnt = store2.getCount();
		for(var i=0; i<cnt; i++) {
			var data = store2.getAt(i).data;
			relations.push(data);
		}
		
		//3、获得查询
		var store3 = this.centerPanel.store;
		cnt = store3.getCount();
		for(var i=0; i<cnt; i++) {
			var data = store3.getAt(i).data;
			querys.push(data);
			
			var zkh = data.zkh ? data.zkh : '';
			var ykh = data.ykh ? data.ykh : '';
			var val = data.val ? data.val : '';			
			html += data.gx+'  ' + zkh + '【'+data.tab+'】.['+data.col + ']  '+data.opt + '  ' + val + ykh + '\n'; 
		}
		
		// 显示内容
		var obj = {tables: tables, relations:relations, querys:querys};
		if(this.textarea && this.json && this.groovy) {
			this.textarea.setValue(html);
			this.json.setValue(Ext.encode(obj));
			this.groovy.setValue("0");
		}
		this.close();
	}
	
});
